﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Configuration;
using Blog.Entity;

[assembly: CLSCompliant(true)]
namespace Blog.DataAccess
{
    public class DataConnection
    {
        private SqlConnection _connection;
        private SqlDataAdapter _dataAdapter;
        private DataTable _dataTable;

        public bool GetConnect()
        {
            const string stringConnect = @"Data Source=(local)\SQLEXPRESS;Initial Catalog=BlogData;Persist Security Info=True;User ID=sa;Password=123456";
            _connection=new SqlConnection();
            _connection.ConnectionString = stringConnect;
            if(_connection.State!=ConnectionState.Open)
                _connection.Open();
            return _connection.State == ConnectionState.Open;
        }
        public void CloseConnection()
        {
            _connection.Close();
        }
        /// <summary>
        /// ham do du lieu vao datatable tu cau truy vanb sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            _dataAdapter=new SqlDataAdapter(sql,_connection);
            _dataTable = new DataTable();
            _dataAdapter.Fill(_dataTable);
            return _dataTable;
        }
        /// <summary>
        /// ham thuc hien cau truy van insert,delete, update
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExecuteQuery(string sql)
        {
            var numRecordsEffect = 0;
            if (!GetConnect())
                return false;
            var command=new SqlCommand(sql,_connection);
            numRecordsEffect = command.ExecuteNonQuery();
            if (numRecordsEffect>0)
            {
                CloseConnection();
                return true;
            }
            return false;
        }
        /// <summary>
        /// Ham lay ma cuoi cung
        /// </summary>
        /// <param name="nameTable"></param>
        /// <param name="nameFiled"></param>
        /// <returns></returns>
        public string GetLastCode(string nameTable,string nameFiled)
        {
            var sql = "SELECT TOP 1 " + nameFiled + " FROM" + nameTable + "ORDER BY" + nameFiled + "DESC";
            GetDataTable(sql);
            return _dataTable.Rows[0][nameFiled].ToString();
        }
        public bool CheckExistValue(string nameTable,string nameFiled,string value)
        {
            var sql = "SELECT * FROM" + nameTable + "WHERE" + nameFiled + "='" + value+"'";
            GetDataTable(sql);
            if (_dataTable.Rows.Count > 0)
                return true;
            return false;
        }
        /// <summary>
        /// Ham do du lieu vao dataset
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql)
        {
            if (!GetConnect())
                return null;
            var dataAdapter=new SqlDataAdapter(sql,_connection);
            var dataSet=new DataSet();
            dataAdapter.Fill(dataSet);
            CloseConnection();
            return dataSet; 
        }
        public DataSet ExecuteDataSet(string storeProcedure, params SqlParameter[] parameters)
        {
            if (!GetConnect())
                return null;
            var command=new SqlCommand(storeProcedure,_connection);
            if(parameters!=null)
            {
               command.Parameters.Clear();
               command.Parameters.AddRange(parameters);
            }
            var dataSet=new DataSet();
            var dataAdapter=new SqlDataAdapter(storeProcedure,_connection);
            command.CommandType = CommandType.StoredProcedure;
            dataAdapter.SelectCommand = command;
            try
            {
                _connection.Open();
                dataAdapter.Fill(dataSet);
            }
            finally
            {
                _connection.Dispose();
            }
            return dataSet;
        }
        public void LogOnUser()
        {
            Entity.LogOn logOn=new LogOn();
            if (!GetConnect())
            {
                return;
            }
            SqlCommand command=new SqlCommand();
            command.CommandText = "LogOn";
#pragma warning disable 612,618
            command.Parameters.Add("@userName", logOn.UserName);
#pragma warning restore 612,618
            command.CommandType = CommandType.StoredProcedure;
            command.Connection = _connection;
            SqlDataAdapter dataAdapter=new SqlDataAdapter(command);
            DataTable dataTable=new DataTable();
            dataAdapter.Fill(dataTable);
            if (dataTable.Rows.Count==0)
            {
                logOn.IsLoged = false;
                logOn.Message = "Dang nhap that bai";
            }
            else if (dataTable.Rows[0][1].ToString()!=logOn.Password)
            {
                logOn.IsLoged = false;
                logOn.Message = "Mat khau sai";
            }
            else
            {
                logOn.IsLoged = true;
                if (dataTable.Rows[0][2].ToString()==logOn.Password)
                {
                    logOn.IsAdmin = true;
                }
            }
        }
    }
}
